package project.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import models.util.MySqlDataAccessHelper;

import project.model.PrComment;

public class CommentDAO {
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public static List getListComPG( int limit, int pageN,String iderr,String db){
		List map = new ArrayList();
		int count=0;
		ArrayList<PrComment> lst =new ArrayList<PrComment>();
		MySqlDataAccessHelper mysql= new MySqlDataAccessHelper();
		
		try{
			mysql.open(db);
			String sql="select count(pr_comment.IDComment) as sl from pr_comment where IDTestProject='"+iderr+"'";
			ResultSet rs=mysql.executeQuery(sql);
			if(rs.next()){
				count=rs.getInt("sl");
			}
			rs.close();
			
			sql="select pr_comment.Comment,pr_comment.DateComment,employee.FullName from pr_comment,employee where pr_comment.IDEmployee=employee.IDEmployee and pr_comment.IDTestProject='"+iderr+"' limit "+limit * (pageN - 1)+","+limit;
			rs=mysql.executeQuery(sql);
			PrComment com;
			while(rs.next()){
				com=new PrComment();
				com.setDb(db);
				//com.setIdComment(rs.getString("IDComment"));
				//com.setIdTestProject(rs.getString("IDTestProject"));
				//com.setIdEmployee(rs.getString("IDEmployee"));
				com.setComment(rs.getString("Comment"));
				com.setDateComment(rs.getDate("DateComment"));
				com.setEmployeeName_(rs.getString("FullName"));
				lst.add(com);
			}
			
			
			map.add(lst);
			map.add(count);
			
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				mysql.close();
			}catch(Exception eeee){
				eeee.printStackTrace();
			}
		}
		return map;
	}
	
	public ArrayList<PrComment> getListCom(String iderr,String db){
		ArrayList<PrComment> lst =new ArrayList<PrComment>();
		MySqlDataAccessHelper mysql= new MySqlDataAccessHelper();
		
		try{
			mysql.open(db);
			String sql="select * from pr_comment where IDTestProject='"+iderr+"'";
			ResultSet rs=mysql.executeQuery(sql);
			PrComment com;
			while(rs.next()){
				com=new PrComment();
				com.setDb(db);
				com.setIdComment(rs.getString("IDComment"));
				com.setIdTestProject(rs.getString("IDTestProject"));
				com.setIdEmployee(rs.getString("IDEmployee"));
				com.setComment(rs.getString("Comment"));
				com.setDateComment(rs.getDate("DateComment"));
				
				lst.add(com);
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}
	
	public PrComment oneCom(String idcom,String db){
		PrComment com=new PrComment();
		MySqlDataAccessHelper mysql= new MySqlDataAccessHelper();
		
		try{
			mysql.open(db);
			String sql="select * from pr_comment where IDComment='"+idcom+"'";
			ResultSet rs=mysql.executeQuery(sql);
			if(rs.next()){	
				com.setDb(db);
				com.setIdComment(rs.getString("IDComment"));
				com.setIdTestProject(rs.getString("IDTestProject"));
				com.setIdEmployee(rs.getString("IDEmployee"));
				com.setComment(rs.getString("Comment"));
				com.setDateComment(rs.getDate("DateComment"));
			}
			rs.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return com;
	}
	
//	public static ArrayList<PrComment> getCommentByIDTestProject(String id,String database){
//		ArrayList<PrComment> temp = new ArrayList<PrComment>();
//        String sql = "SELECT c.IDComment,c.IDTestProject,c.IDEmployee,c.`Comment`,c.DateComment,e.IDEmployee,e.FullName" +
//        		"	FROM pr_comment c, employee e" +
//        		"	WHERE c.IDEmployee = e.IDEmployee and c.IDTestProject = '"+id+"' ORDER BY IDComment ASC";
//        MySqlDataAccessHelper help = new MySqlDataAccessHelper();
//        try {
//        	help.open(database);		
////			PreparedStatement cs = help.getConnection().prepareStatement(sql);
////			cs.setString(1, id);
//			ResultSet rs = help.executeQuery(sql);         
//            while(rs.next()){       
//            	temp.add(new PrComment(rs.getString("IDComment"), rs.getString("IDTestProject"),rs.getString("IDEmployee"),
//            			rs.getString("Comment"), rs.getTimestamp("DateComment"),
//            			rs.getString("FullName")));        	
//            }    
//        } catch (Exception ex){
//            ex.printStackTrace(System.out);
//        }finally{
//			help.close();
//		}
//        return temp;
//	}
	
	public static void deleteItemComment(String databasename,String comment){
		MySqlDataAccessHelper hep = new MySqlDataAccessHelper();
		try{
			hep.open(databasename);
			String sql="delete from pr_comment where IDComment=?";
			PreparedStatement pr=hep.getConnection().prepareStatement(sql);
			pr.setString(1, comment);
			pr.executeUpdate();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
	}
	public static void deleteComment(String idTest,String database){
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        MySqlDataAccessHelper herlp = new MySqlDataAccessHelper();
		String sql = "DELETE FROM pr_comment WHERE pr_comment.IDTestProject = ?";
        try {
        	herlp.open(database);
            pstmt = herlp.getConnection().prepareStatement(sql);
            pstmt.setString(1, idTest);
            pstmt.executeUpdate();           
        } catch (Exception ex){
            ex.printStackTrace(System.out);
        }finally {
            herlp.close();
        }
	}
	
	public static void addNewCommentErrorDAO_tc(String database,String idproject,String idemployee,String comment,String namecolor){
		MySqlDataAccessHelper hep = new MySqlDataAccessHelper();
		try{
			hep.open(database);
			DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
			String id = new Date().getTime() + "";
			comment += "-%$#@!-" + namecolor;
			String sql = "insert into pr_comment(IDComment,IDTestProject,IDEmployee,Comment,DateComment) " +
       			 "VALUES(?,?,?,?,?)";
			PreparedStatement pr=hep.getConnection().prepareStatement(sql);
			pr.setString(1, id);
            pr.setString(2, idproject);
            pr.setString(3, idemployee);
            pr.setString(4, comment);
            pr.setString(5, format.format(new Date()));
            pr.executeUpdate();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			hep.close();
		}
	}
	
	public static ArrayList<PrComment> getCommentByIDTestProject(String id,String database){
		ArrayList<PrComment> temp = new ArrayList<PrComment>();
        String sql = "SELECT c.IDComment,c.IDTestProject,c.IDEmployee,c.`Comment`,c.DateComment,e.IDEmployee,e.FullName" +
        		"	FROM pr_comment c, employee e" +
        		"	WHERE c.IDEmployee = e.IDEmployee and c.IDTestProject = '"+id+"' ORDER BY IDComment ASC";
        MySqlDataAccessHelper help = new MySqlDataAccessHelper();
        PrComment prComment ;
        try {
        	help.open(database);		
			ResultSet rs = help.executeQuery(sql);         
            while(rs.next()){       
            	prComment = new PrComment();
//            	prComment.setDb(database);
            	prComment.setIdComment(rs.getString("IDComment"));
            	prComment.setIdTestProject(rs.getString("IDTestProject"));
            	prComment.setIdEmployee(rs.getString("IDEmployee"));
            	prComment.setComment(rs.getString("Comment"));
            	prComment.setDateComment(rs.getDate("DateComment"));
//            	prComment.setEmployeeName(rs.getString("FullName"));
            	prComment.setNameEployee(rs.getString("FullName"));
            	temp.add(prComment);
//            	temp.add(new PrComment(rs.getString("IDComment"), rs.getString("IDTestProject"),rs.getString("IDEmployee"),
//            			rs.getString("Comment"), rs.getTimestamp("DateComment"),
//            			rs.getString("FullName")));        	
            }    
        } catch (Exception ex){
            ex.printStackTrace(System.out);
        }finally{
			help.close();
		}
        return temp;
	}
}
